package site.sorghum.ddl.dialect;

import site.sorghum.ddl.DDLDialect;
import site.sorghum.ddl.entity.DdlColumnWrap;
import site.sorghum.ddl.entity.DdlIndexWrap;
import site.sorghum.ddl.entity.DdlTableWrap;
import site.sorghum.ddl.field.BasicTypeConverter;
import site.sorghum.ddl.field.TypeConverter;

import java.util.ArrayList;
import java.util.List;
import java.util.Objects;
import java.util.function.Function;
import java.util.stream.Collectors;

/**
 * 抽象DDL方言基类
 */
public abstract class AbstractDDLDialect implements DDLDialect {

    @Override
    public String generateAddColumnDDL(DdlColumnWrap column) {
        return String.format("ALTER TABLE %s ADD COLUMN %s %s%s%s%s;",
                quoteIdentifier(column.getTable()),
                quoteIdentifier(column.getName()),
                getColumnType(column),
                getDefaultValueClause(column),
                getNullableClause(column),
                getColumnCommentClause(column));
    }


    @Override
    public List<String> generateCreateTableDDL(DdlTableWrap table) {
        List<String> result = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        // 创建表语句
        sql.append("CREATE TABLE ").append(quoteIdentifier(table.getName())).append(" (\n");

        // 添加列定义
        List<DdlColumnWrap> ddlColumnWraps = table.getColumns();
        for (int i = 0; i < ddlColumnWraps.size(); i++) {
            DdlColumnWrap column = ddlColumnWraps.get(i);
            sql.append("  ").append(quoteIdentifier(column.getName())).append(" ")
                    .append(getColumnType(column))
                    .append(getNullableClause(column))
                    .append(getDefaultValueClause(column))
                    .append(getColumnCommentClause(column));

            // 如果不是最后一列，添加逗号
            if (i < ddlColumnWraps.size() - 1) {
                sql.append(",");
                sql.append("\n");
            }
        }

        // 添加主键定义
        List<String> primaryKeyColumns = new ArrayList<>();
        table.getPks().forEach(pk -> primaryKeyColumns.add(quoteIdentifier(pk)));
        if (!primaryKeyColumns.isEmpty()) {
            sql.append(",\n").append("  ").append("PRIMARY KEY (")
                    .append(String.join(", ", primaryKeyColumns))
                    .append(")\n");
        }

        sql.append(")");

        // 添加表注释
        if (table.getRemarks() != null && !table.getRemarks().isEmpty()) {
            sql.append(" COMMENT ").append(quoteValue(table.getRemarks()));
        }

        if (!Objects.equals(sql.charAt(sql.length() - 1), ';')) {
            sql.append(";");
        }
        result.add(sql.toString());
        for (DdlIndexWrap index : table.getIndexes()) {
            String generateIndexDDL = generateAddIndexDDL(index);
            result.add(generateIndexDDL);
        }
        return result;
    }

    @Override
    public String generateAddIndexDDL(String indexName,
                                      List<DdlColumnWrap> columnWraps,
                                      boolean unique) {
        if (columnWraps.isEmpty()) {
            return "";
        }
        String tableName = columnWraps.get(0).getTable();
        StringBuilder sql = new StringBuilder();
        sql.append("CREATE ");
        if (unique) {
            sql.append("UNIQUE ");
        }
        if (indexName == null || indexName.isEmpty()) {
            indexName = DdlIndexWrap.columnUniqueIndexName(columnWraps.stream().map(DdlColumnWrap::getName).collect(Collectors.toList()), unique);
        }
        sql.append("INDEX ").append(quoteIdentifier(indexName)).append(" ON ")
                .append(quoteIdentifier(tableName)).append(" (")
                .append(columnWraps.stream().map(DdlColumnWrap::getName).map(this::quoteIdentifier).collect(Collectors.joining(", ")))
                .append(")");
        sql.append(";");
        return sql.toString();
    }


    @Override
    public String generateDropColumnDDL(String table, String column) {
        return String.format("ALTER TABLE %s DROP COLUMN %s;",
                quoteIdentifier(table),
                quoteIdentifier(column));
    }

    @Override
    public String generateDropIndexDDL(String table, String indexName) {
        return String.format("DROP INDEX %s ON %s;",
                quoteIdentifier(indexName),
                quoteIdentifier(table));
    }

    @Override
    public DdlTableWrap fromDataSource(
            String schema,
            String tableName,
            Function<String, List<String>> pks,
            Function<String, DdlTableWrap> table,
            Function<String, List<DdlColumnWrap>> columns,
            Function<String, List<DdlIndexWrap>> indexes) {
        // 生成获取当前表信息的语句SQL
        String tableSql = String.format("SELECT TABLE_NAME    AS name,\n" +
                "       TABLE_COMMENT AS remarks\n" +
                "FROM INFORMATION_SCHEMA.TABLES\n" +
                "WHERE TABLE_NAME = '%s'\n" +
                "  and TABLE_SCHEMA = '%s';", tableName, schema);
        // 生成获取列信息的SQL
        String columnSql = String.format("\n" +
                "SELECT %s                   as tableName,\n" +
                "       COLUMN_NAME              AS name,\n" +
                "       DATA_TYPE                AS dbType,\n" +
                "       CHARACTER_MAXIMUM_LENGTH AS size,\n" +
                "       NUMERIC_PRECISION        AS digit,\n" +
                "       IS_NULLABLE              AS isNullable,\n" +
                "       if(COLUMN_DEFAULT IS NOT NULL && UPPER(COLUMN_DEFAULT) <> 'NULL', COLUMN_DEFAULT, null)" +
                "                                AS defaultValue,\n" +
                "       COLUMN_COMMENT           AS remarks\n" +
                "FROM INFORMATION_SCHEMA.COLUMNS\n" +
                "WHERE TABLE_NAME = '%s'\n" +
                "  and TABLE_SCHEMA = '%s';", quoteValue(tableName), tableName, schema);
        // 生成主键获取信息的SQL
        String pksSql = String.format("SELECT COLUMN_NAME AS name\n" +
                "FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE\n" +
                "WHERE TABLE_NAME = '%s'\n" +
                "  AND CONSTRAINT_NAME = 'PRIMARY'\n" +
                "  and TABLE_SCHEMA = '%s'", tableName, schema);
        // 生成索引获取信息的SQL
        String indexSql = String.format("SELECT %s AS tableName,\n" +
                "       INDEX_NAME AS indexName,\n" +
                "       GROUP_CONCAT(COLUMN_NAME ORDER BY SEQ_IN_INDEX) AS columnNames,\n" +
                "       NON_UNIQUE = 0 AS isUnique\n" +
                "FROM INFORMATION_SCHEMA.STATISTICS\n" +
                "WHERE TABLE_NAME = %s\n" +
                "  AND TABLE_SCHEMA = %s\n" +
                "  AND INDEX_NAME != 'PRIMARY'\n" +
                "GROUP BY TABLE_NAME, INDEX_NAME, NON_UNIQUE", quoteValue(tableName), quoteValue(tableName), quoteValue(schema));
        DdlTableWrap tableWrap = table.apply(tableSql);
        List<DdlColumnWrap> columnWraps = columns.apply(columnSql);
        List<String> pkList = pks.apply(pksSql);
        List<DdlIndexWrap> indexWraps = indexes.apply(indexSql);
        tableWrap.setColumns(columnWraps);
        tableWrap.setPks(pkList);
        tableWrap.setIndexes(indexWraps);
        return tableWrap;
    }

    @Override
    public List<String> fetchAllTableNames(String schema, Function<String, List<String>> tables) {
        // 生成获取所有表名的SQL
        String sql = String.format("SELECT TABLE_NAME\n" +
                "FROM INFORMATION_SCHEMA.TABLES\n" +
                "WHERE TABLE_SCHEMA = '%s';", schema);
        // 使用传入的函数获取表名列表
        return tables.apply(sql);
    }

    @Override
    public TypeConverter typeConverter() {
        return BasicTypeConverter.INSTANCE;
    }

    /**
     * 获取列类型定义
     */
    protected String getColumnType(DdlColumnWrap column) {
        String typeName = column.getSqlType(typeConverter()).toUpperCase();

        if (column.getSize() != null && column.getSize() > 0) {
            if (column.getDigit() != null && column.getDigit() > 0) {
                return String.format("%s(%d,%d)", typeName, column.getSize(), column.getDigit());
            }
            return String.format("%s(%d)", typeName, column.getSize());
        }
        return typeName;
    }

    /**
     * 获取默认值子句
     * 注意：ColumnWrap 类中没有 defaultValue 字段，这里使用 remarks 作为示例
     */
    protected String getDefaultValueClause(DdlColumnWrap column) {
        if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
            return " DEFAULT " + quoteValue(column.getRemarks());
        }
        return "";
    }

    /**
     * 获取列注释子句
     */
    protected String getColumnCommentClause(DdlColumnWrap column) {
        if (column.getRemarks() != null && !column.getRemarks().isEmpty()) {
            return " COMMENT " + quoteValue(column.getRemarks());
        }
        return "";
    }


    /**
     * 获取可为空子句
     */
    protected String getNullableClause(DdlColumnWrap column) {
        return Boolean.TRUE.equals(column.getIsNullable()) ? "" : " NOT NULL";
    }

    /**
     * 引用标识符（表名、列名等）
     */
    protected abstract String quoteIdentifier(String identifier);

    /**
     * 引用值
     */
    protected abstract String quoteValue(Object value);

}